package pl.szpadel.android.gadu;

import java.util.Collection;
import java.util.LinkedList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/// Manages creating/update of contacts database
public class ContactDatabaseOpenHelper extends SQLiteOpenHelper {

	static private final String TAG = "ContactDatabaseOpenHelper";
	
	// constant, use when building queries
	static public final String TABLE_CONTACTS = "contatcs";
	static public final String COLUMN_USER_UIN = "useruin"; // uin of current user
	static public final String COLUMN_CONTACT_UIN = "uin"; // uin of the contact
	static public final String COLUMN_DISPLAY_NAME = "display";
	static public final String COLUMN_MOBILE_PHONE = "mobile";
	static public final String COLUMN_HOME_PHONE = "home";
	static public final String COLUMN_FIRST_NAME = "firstname";
	static public final String COLUMN_LAST_NAME = "lastname";
	static public final String COLUMN_CITY = "city";
	static public final String COLUMN_GENDER = "gender";
	static public final String COLUMN_BIRTH = "birth";
	static public final String COLUMN_EMAIL = "email";
	static public final String COLUMN_WEBPAGE = "webpage";
	
	// db params
	static private final int DBVERSION = 1;
	static private final String DBNAME = "contacts.sqlite";
	
	
	public ContactDatabaseOpenHelper(Context ctx) {
		super(ctx, DBNAME, null, DBVERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// create table here
		String createQuery = 
			"CREATE TABLE " + TABLE_CONTACTS + " ( " +
				COLUMN_USER_UIN + " INTEGER, " +
				COLUMN_CONTACT_UIN + " INTEGER, " +
				COLUMN_DISPLAY_NAME + " TEXT, " +
				COLUMN_MOBILE_PHONE + " TEXT, " +
				COLUMN_HOME_PHONE + " TEXT, " +
				COLUMN_FIRST_NAME + " TEXT, " +
				COLUMN_LAST_NAME + " TEXT, " +
				COLUMN_CITY + " TEXT, " +
				COLUMN_GENDER + " TEXT, " +
				COLUMN_BIRTH + " TEXT, " +
				COLUMN_EMAIL + " TEXT, " +
				COLUMN_WEBPAGE + " TEXT, " + 
				"PRIMARY KEY (" + COLUMN_USER_UIN + "," + COLUMN_CONTACT_UIN + "));";
		
		db.execSQL(createQuery);
	}
	

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// do nothing, we are on lvl 1
	}
	
	/// Inserts contact info into db
	public void insert(ContactInfo contact) {
		String insertQuery = "";
		try {
			SQLiteDatabase db = getWritableDatabase();
			
			insertQuery = 
				"INSERT INTO " + TABLE_CONTACTS + " ( " +
					COLUMN_USER_UIN + " , " +
					COLUMN_CONTACT_UIN + " , " +
					COLUMN_DISPLAY_NAME + " , " +
					COLUMN_MOBILE_PHONE + " , " +
					COLUMN_HOME_PHONE + " , " +
					COLUMN_FIRST_NAME + " , " +
					COLUMN_LAST_NAME + " , " +
					COLUMN_CITY + " , " +
					COLUMN_GENDER + " , " +
					COLUMN_BIRTH + " , " +
					COLUMN_EMAIL + " , " +
					COLUMN_WEBPAGE 
				+ " ) VALUES ( " +
					App.getInstance().getConfig().getUin() + "," +
					contact.id + "," +
					"'" + contact.displayName + "'," +
					"'" + contact.mobilePhone + "'," +
					"'" + contact.homePhone + "'," +
					"'" + contact.firstName + "'," +
					"'" + contact.lastName + "'," +
					"'" + contact.city + "'," +
					"'" + contact.gender + "'," +
					"'" + contact.birth + "'," +
					"'" + contact.email + "'," +
					"'" + contact.wwwAdrress + "');";
			
			db.execSQL(insertQuery);
		} catch (Exception e) {
			AGLog.e(TAG, "Error inserting contact to DB, error=" + e + "query=" + insertQuery);
		}
	}
	
	public void update(ContactInfo contact) {
		String updateQuery = 
			"UPDATE " + TABLE_CONTACTS + " SET " +
				COLUMN_DISPLAY_NAME + "='" + contact.displayName + "', " +
				COLUMN_MOBILE_PHONE + "='" + contact.mobilePhone + "'," +
				COLUMN_HOME_PHONE + "='" + contact.homePhone + "'," +
				COLUMN_FIRST_NAME +"='" + contact.firstName + "'," +
				COLUMN_LAST_NAME + "='" + contact.lastName + "'," +
				COLUMN_CITY + "='" + contact.city + "'," +
				COLUMN_GENDER + "='" + contact.gender + "'," +
				COLUMN_BIRTH + "='" + contact.birth + "'," +
				COLUMN_EMAIL + "='" + contact.email + "'," +
				COLUMN_WEBPAGE + "='" + contact.wwwAdrress + "'"
			+ " WHERE " +
				COLUMN_USER_UIN + "=" + App.getInstance().getConfig().getUin() + " AND " +
				COLUMN_CONTACT_UIN + "=" + contact.id + ";";
		try {
			SQLiteDatabase db = getWritableDatabase();
			
			db.execSQL(updateQuery);
		} catch (Exception e) {
			AGLog.e(TAG, "Error updating contact to DB, error=" + e + "query=" + updateQuery);
		}
	}
	
	public Collection<ContactInfo> getAllContacts() {
		LinkedList<ContactInfo> contacts = new LinkedList<ContactInfo>();
		
		try {
			SQLiteDatabase db = getReadableDatabase();
			
			Cursor c = db.query(TABLE_CONTACTS, null, COLUMN_USER_UIN+"="+App.getInstance().getConfig().getUin(), null, null, null, null);
			if (c.moveToFirst()) {
				do {
					long uin = c.getLong(c.getColumnIndex(COLUMN_CONTACT_UIN));
					String displayName  = c.getString(c.getColumnIndex(COLUMN_DISPLAY_NAME));
					
					ContactInfo info = new ContactInfo(displayName, uin);
					
					info.birth = c.getString(c.getColumnIndex(COLUMN_BIRTH));
					info.email = c.getString(c.getColumnIndex(COLUMN_EMAIL));
					info.wwwAdrress = c.getString(c.getColumnIndex(COLUMN_WEBPAGE));
					info.city = c.getString(c.getColumnIndex(COLUMN_CITY));
					info.gender = c.getString(c.getColumnIndex(COLUMN_GENDER));
					info.firstName = c.getString(c.getColumnIndex(COLUMN_FIRST_NAME));
					info.lastName = c.getString(c.getColumnIndex(COLUMN_LAST_NAME));
					info.mobilePhone = c.getString(c.getColumnIndex(COLUMN_MOBILE_PHONE));
					info.homePhone = c.getString(c.getColumnIndex(COLUMN_HOME_PHONE));
					
					contacts.add(info);
					
				} while(c.moveToNext());
			}
			
		} catch (Exception e) {
			AGLog.e(TAG, "Error reading contacts from DB, error=" + e);
		}
		
		return contacts;
	}
	
	public void deleteContact(long uin) {
		String deleteQuery = 
			"DELETE FROM " + TABLE_CONTACTS +
			" WHERE " + COLUMN_CONTACT_UIN + "=" + uin +
			" AND " + COLUMN_USER_UIN + "=" + App.getInstance().getConfig().getUin() + ";";
		try {
			SQLiteDatabase db = getWritableDatabase();
			
			db.execSQL(deleteQuery);
		} catch (Exception e) {
			AGLog.e(TAG, "Error deleting contact from DB, error=" + e + "query=" + deleteQuery);
		}
	}
}
